<?php
namespace Home\Controller;

use Think\Controller;

class ExcelController extends Controller
{

    public function index()
    {}

    public function upload($file, $ext, $fs)
    {
        import("Common.Org.PHPExcel");
        import("Common.Org.PHPExcel.Writer.Excel5");
        import("Common.Org.PHPExcel.IOFactory.php");
        import("Common.Org.PHPExcel.Reader.Excel5");
        $objPHPExcel = new \PHPExcel();
        
        $file_name = $file;
        if ($ext == "xlsx") {
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        } else {
            
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }
        $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数
        $arrExcel = $objPHPExcel->getSheet(0)->toArray();
        if ($fs == 'px') {
            $data['zbdw'] = $objPHPExcel->getActiveSheet()
                ->getCell("C2")
                ->getValue();
            $data['bcbh'] = $objPHPExcel->getActiveSheet()
                ->getCell("K2")
                ->getValue();
            $data['classlb'] = $objPHPExcel->getActiveSheet()
                ->getCell("K5")
                ->getValue();
            $data['jteacher'] = $objPHPExcel->getActiveSheet()
                ->getCell("K3")
                ->getValue();
            $data['dteacher'] = $objPHPExcel->getActiveSheet()
                ->getCell("K4")
                ->getValue();
            $data['bcmc'] = $objPHPExcel->getActiveSheet()
                ->getCell("C3")
                ->getValue();
            $data['xygc'] = $objPHPExcel->getActiveSheet()
                ->getCell("C4")
                ->getValue();
            $data['xyrs'] = $objPHPExcel->getActiveSheet()
                ->getCell("D5")
                ->getValue();
            $data['ddrs'] = $objPHPExcel->getActiveSheet()
                ->getCell("F5")
                ->getValue();
            $data['sjrs'] = $objPHPExcel->getActiveSheet()
                ->getCell("H5")
                ->getValue();
            $data['zjrs'] = $objPHPExcel->getActiveSheet()
                ->getCell("J5")
                ->getValue();
            $data['sbjrs'] = $objPHPExcel->getActiveSheet()
                ->getCell("D6")
                ->getValue();
            $data['jtjrs'] = $objPHPExcel->getActiveSheet()
                ->getCell("F6")
                ->getValue();
            $data['xcjrs'] = $objPHPExcel->getActiveSheet()
                ->getCell("H6")
                ->getValue();
            $data['kjyxrs'] = $objPHPExcel->getActiveSheet()
                ->getCell("J6")
                ->getValue();
            $data['ddsj'] = $objPHPExcel->getActiveSheet()
                ->getCell("E7")
                ->getValue();
            $data['lksj'] = $objPHPExcel->getActiveSheet()
                ->getCell("I7")
                ->getValue();
            $data['bdfs'] = $objPHPExcel->getActiveSheet()
                ->getCell("D9")
                ->getValue();
            $data['djs'] = $objPHPExcel->getActiveSheet()
                ->getCell("D14")
                ->getValue();
            $data['bjs'] = $objPHPExcel->getActiveSheet()
                ->getCell("F14")
                ->getValue();
            $data['cyjj'] = $objPHPExcel->getActiveSheet()
                ->getCell("H14")
                ->getValue();
            $data['lxrxm'] = $objPHPExcel->getActiveSheet()
                ->getCell("D15")
                ->getValue();
            $data['yx'] = $objPHPExcel->getActiveSheet()
                ->getCell("H15")
                ->getValue();
            $data['lxrzw'] = $objPHPExcel->getActiveSheet()
                ->getCell("D16")
                ->getValue();
            $data['lxrsj'] = $objPHPExcel->getActiveSheet()
                ->getCell("D17")
                ->getValue();
            $data['gddh'] = $objPHPExcel->getActiveSheet()
                ->getCell("H17")
                ->getValue();
            $data['pxfxq'] = $objPHPExcel->getActiveSheet()
                ->getCell("B18")
                ->getValue();
            $data['qrxx'] = $objPHPExcel->getActiveSheet()
                ->getCell("B23")
                ->getValue();
            
            $data['gtsj'] = $objPHPExcel->getActiveSheet()
                ->getCell("E11")
                ->getValue();
            $data['gtbc'] = $objPHPExcel->getActiveSheet()
                ->getCell("F11")
                ->getValue();
            $data['gtfcsj'] = $objPHPExcel->getActiveSheet()
                ->getCell("H11")
                ->getValue();
            $data['gtfcbc'] = $objPHPExcel->getActiveSheet()
                ->getCell("J11")
                ->getValue();
            $data['jcsj'] = $objPHPExcel->getActiveSheet()
                ->getCell("E12")
                ->getValue();
            $data['jcbc'] = $objPHPExcel->getActiveSheet()
                ->getCell("F12")
                ->getValue();
            $data['jcfcsj'] = $objPHPExcel->getActiveSheet()
                ->getCell("H12")
                ->getValue();
            $data['jcfcbc'] = $objPHPExcel->getActiveSheet()
                ->getCell("J12")
                ->getValue();
            $data['qtsj'] = $objPHPExcel->getActiveSheet()
                ->getCell("E13")
                ->getValue();
            $data['qtbc'] = $objPHPExcel->getActiveSheet()
                ->getCell("F13")
                ->getValue();
            $data['qtfcsj'] = $objPHPExcel->getActiveSheet()
                ->getCell("H13")
                ->getValue();
            $data['qtfcbc'] = $objPHPExcel->getActiveSheet()
                ->getCell("J13")
                ->getValue();
            $data['classes_num'] = $objPHPExcel->getActiveSheet()
                ->getCell("H2")
                ->getValue();
        } elseif ($fs == 'xy') {
            
            for ($i = 3; $i <= $highestRow; $i ++) {
                
                $data[$i]['loginname'] = $objPHPExcel->getActiveSheet()
                    ->getCell("B" . $i)
                    ->getValue();
                $data[$i]['sex'] = $objPHPExcel->getActiveSheet()
                    ->getCell("C" . $i)
                    ->getValue();
                $data[$i]['nation'] = $objPHPExcel->getActiveSheet()
                    ->getCell("D" . $i)
                    ->getValue();
                $data[$i]['Company'] = $objPHPExcel->getActiveSheet()
                    ->getCell("E" . $i)
                    ->getValue();
                $data[$i]['telphone'] = $objPHPExcel->getActiveSheet()
                    ->getCell("F" . $i)
                    ->getValue();
                $data[$i]['IDcard'] = $objPHPExcel->getActiveSheet()
                    ->getCell("G" . $i)
                    ->getValue();
                $data[$i]['level'] = $objPHPExcel->getActiveSheet()
                    ->getCell("H" . $i)
                    ->getValue();
                $data[$i]['remark'] = $objPHPExcel->getActiveSheet()
                    ->getCell("I" . $i)
                    ->getValue();
            }
        }
        
        return $data;
    }

    public function out($sql, $fs, $key)
    {
        // 读取库里所有的表
        if ($fs == 'teacher') {
            $ziduan = array(
                "ID",
                "教师工号",
                "教师姓名",
                "性别",
                "籍贯",
                "入职日期",
                "生日",
                "电话",
                "E-mail",
                "备注",
                "备注2"
            );
        } elseif ($fs == 'student') {
            $ziduan = array(
                "ID",
                "学员号",
                "学员姓名",
                "性别",
                "班次编号",
                "籍贯",
                "所在单位",
                "职务",
                "生日",
                "身份证号码",
                "电话",
                "E-mail",
                "类别",
                "备注",
                "备注2"
            );
        } elseif ($fs == 'classes') {
            
            $ziduan = array(
                "ID",
                "班次编号",
                "班次名称",
                "班次课程",
                "班次地区",
                "接班老师",
                "带班老师",
                "备注",
                "备注2",
                "主办单位"
            );
        } elseif ($fs == "Curr") {
            
            $ziduan = array(
                "ID",
                "班次编号",
                "课程名称",
                "日期",
                "授课人",
                "授课内容",
                "教室",
                "人数",
                "金额",
                "备注",
                "备注2",
                "午别"
            );
        }
        
        $res = $sql;
        if (! empty($key) && $fs == 0) {
            $data[] = array(
                $key
            );
        } else {
            $data[][0] = $ziduan;
        }
        
        $data[] = M()->query($res);
        $data[][] = array();
        
        // 导入PHPExcel类库
        import("Common.Org.PHPExcel");
        import("Common.Org.PHPExcel.Writer.Excel5");
        import("Common.Org.PHPExcel.IOFactory.php");
        $filename = "gbschool_excel";
        $this->getExcel($filename, $data);
    }

    private function getExcel($fileName, $data)
    {
        // 对数据进行检验
        if (empty($data) || ! is_array($data)) {
            die("data must be a array");
        }
        $date = date("Y_m_d", time());
        $fileName .= "_{$date}.xls";
        // 创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();
        
        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        $objPHPExcel->getActiveSheet()
            ->getStyle()
            ->getFont()
            ->setName('微软雅黑'); // 设置字体
        $objPHPExcel->getActiveSheet()
            ->getDefaultRowDimension()
            ->setRowHeight(15); // 设置默认高度
                                
        // $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('5');//设置列宽
                                // $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('22');//设置列宽
                                // $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('22');//设置列宽
                                // $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth('22');//设置列宽
                                // $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth('22');//设置列宽
                                // $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth('22');//设置列宽
                                // $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth('40');//设置列宽
                                
        // 设置边框
        $sharedStyle1 = new \PHPExcel_Style();
        $sharedStyle1->applyFromArray(array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN
                )
            )
        ));
        
        foreach ($data as $ke => $row) {
            
            foreach ($row as $key => $rows) {
                
                if (count($row) == 1 && empty($row[0][1]) && empty($rows[1]) && ! empty($rows)) {
                    
                    // $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A{$column}:J{$column}");//设置边框
                    array_unshift($rows, $rows['0']);
                    // $objPHPExcel->getActiveSheet()->mergeCells("A{$column}:J{$column}");//合并单元格
                    $objPHPExcel->getActiveSheet()
                        ->getStyle("A{$column}:J{$column}")
                        ->getFont()
                        ->setSize(12); // 字体
                                           // $objPHPExcel->getActiveSheet()->getStyle("A{$column}:J{$column}")->getFont()->setBold(true);//粗体
                                           
                    // 背景色填充
                                           // $objPHPExcel->getActiveSheet()->getStyle("A{$column}:J{$column}")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                                           // $objPHPExcel->getActiveSheet()->getStyle("A{$column}:J{$column}")->getFill()->getStartColor()->setARGB('FFB8CCE4');
                } else {}
                
                if ($rows['1'] == '字段') {
                    $rows[0] = 'ID';
                    // 背景色填充
                    // $objPHPExcel->getActiveSheet()->getStyle("A{$column}:J{$column}")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                    // $objPHPExcel->getActiveSheet()->getStyle("A{$column}:J{$column}")->getFill()->getStartColor()->setARGB('FF4F81BD');
                }
                
                $objPHPExcel->getActiveSheet()
                    ->getStyle("A{$column}:J{$column}")
                    ->getAlignment()
                    ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); // 垂直居中
                $objPHPExcel->getActiveSheet()
                    ->getStyle("A{$column}:J{$column}")
                    ->getAlignment()
                    ->setWrapText(true); // 换行
                                         // 行写入
                $span = ord("A");
                $span2 = ord("@");
                foreach ($rows as $keyName => $value) {
                    // 列写入
                    if ($span > ord("Z")) {
                        $span2 += 1;
                        $span = ord("A");
                        $j = chr($span2) . chr($span); // 超过26个字母时才会启用
                    } else {
                        if ($span2 >= ord("A")) {
                            $j = chr($span2) . chr($span); // 超过26个字母时才会启用
                        } else {
                            $j = chr($span);
                        }
                    }
                    ;
                    
                    if (eregi("^[0-9]+$", $value) && strlen($value) > 10) {
                        
                        $objActSheet->setCellValue($j . $column, "'" . $value);
                    } else {
                        $objActSheet->setCellValue($j . $column, $value);
                    }
                    
                    $span ++;
                }
                $column ++;
            }
        }
        $fileName = iconv("utf-8", "gb2312", $fileName);
        // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); // 文件通过浏览器下载
        exit();
    }
}